package masterpiece.util;

import java.sql.*;
import java.util.Scanner;
import java.io.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;

/**
 * Utility class for testing certain use cases. See Deliverable 4 for details.
 * 
 * @author Fredrik
 * @author Travis
 * @author Brad
 * @author Chris
 */
public class Utilities {

	/** The URL for the database. */
	private final String DATABASE_URL = "jdbc:oracle:thin:@moe.cs.plu.edu:1521:plucsce";

	/** The default user name. */
	private final String DEFAULT_USERNAME = "2009mast";

	/** The default password. */
	private final String DEFAULT_PASSWORD = "mast";

	/** Connection object used throughout the class. */
	private Connection conn = null;

	/**
	 * Opens the connection to the database, enabling use of the Connection
	 * object. Uses the default user name and password.
	 * 
	 * @throws SQLException
	 *             upon error in connecting to the database.
	 * @throws ClassNotFoundException
	 *             if the Oracle JDBC driver cannot be loaded.
	 * @author Fredrik
	 */
	public void openDB() throws SQLException, ClassNotFoundException {
		openDB(DEFAULT_USERNAME, DEFAULT_PASSWORD);
	}

	/**
	 * Opens the connection to the database, enabling use of the Connection
	 * object. Uses the user name and password given.
	 * 
	 * @param username
	 *            The user name with which to connect.
	 * @param password
	 *            The password with which to connect.
	 * @throws SQLException
	 *             upon error in connecting to the database.
	 * @throws ClassNotFoundException
	 *             if the Oracle JDBC driver cannot be loaded.
	 * @author Fredrik
	 */
	public void openDB(String username, String password) throws SQLException,
			ClassNotFoundException {
		// Load the Oracle JDBC driver
		Class.forName("oracle.jdbc.driver.OracleDriver");

		// Connect to the database
		conn = DriverManager.getConnection(DATABASE_URL, username, password);
		conn.setAutoCommit(false);
	}

	/**
	 * This method submits an article with all the correct data into the DB	 * 
	 * @author Travis Towne
	 * 
	 * @param ePass
	 * @param headline
	 * @param sub_headline
	 * @param article_date
	 * @param section
	 * @param body
	 * @param issue
	 */
	public void submitArticle (String ePass, String headline,
							   String sub_headline, java.sql.Date article_date,
							   String section, String body, 
							   java.sql.Date issue) {
		try {// create a Statement and an SQL string for the statement
			String sql = null;
			PreparedStatement pstmt = null;

			sql = "INSERT INTO article " +
				  "VALUES(id_increment.nextval, ?, ?, ?, ?, -1, ?, ?, 1, ?)"; 
			pstmt = conn.prepareStatement(sql);
			
			int index = 1;
			pstmt.setString(index, headline);
			index++;
			pstmt.setString(index, sub_headline);
			index++;
			pstmt.setDate(index, article_date);
			index++;
			pstmt.setString(index, section);
			index++;
			pstmt.setString(index, body);
			index++;
			pstmt.setDate(index, issue);
			index++;
			String[] bodyWords = body.toString().split("\\s+");
			pstmt.setInt(index, bodyWords.length);

			pstmt.execute();
			conn.commit();

			sql = "INSERT INTO authorship " +
				  "VALUES(?, id_increment.CURRVAL)";
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, ePass);
			
			pstmt.execute();
			conn.commit();
		} catch(Exception e) {
			System.out.println("caught exception " + e.toString());
		}
	}
	
	/**
	 * This creates a result set from the database of articles
	 * that are in draft or revised status
	 * 
	 * @author Travis Towne
	 * 
	 * @return a ResultSet of the articles in the database 
	 * that have a 1 or 2 in the status column
	 */
	public ResultSet getUpdateList() {
		try {
			String sql;
			PreparedStatement pstmt;
			
			sql = "SELECT id, headline, first_name, last_name " +
				  "FROM article, users, authorship " +
				  "WHERE id = authorship_article and authorship_users = epass " +
				  "and status > 0 and status < 3";
			
			pstmt = conn.prepareStatement(sql);
			
			return pstmt.executeQuery();
		} catch(Exception e) {
			System.out.println("caught exception" + e.toString());
			return null;
		}
	}
	
	/**
	 * updates the article that corresponds to the id passed
	 * and edits the section the corresponds to the edit section
	 * that was passed to it as well
	 * 
	 * @author Travis Towne
	 * 
	 * @param id
	 * @param edit
	 */
	public void updateArticle (String epass, int id, int edit) {
		try {
			String sql, editText = "";
			PreparedStatement pstmt;
			ResultSet rset;
			Scanner in = new Scanner(System.in);
			
			switch(edit){
				case 1:editText = "headline";break;
				case 2:editText = "sub_headline";break;
				case 3:editText = "body";break;
				case 4:editText = "section";break;
				case 5:editText = "rank";break;
				case 6:editText = "status";break;
			}
			
			sql = "SELECT " + editText + " " +
				  "FROM article " +
				  "WHERE id = ?";
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setInt(1, id);
			
			rset = pstmt.executeQuery();
			conn.commit();
			
			if(rset.next()) {
				sql = "UPDATE article SET "; 
			
				switch(edit) {
					case 1:editText = rset.getClob("headline").getSubString(1l, (int)rset.getClob("headline").length());
						   sql +=  "headline = ? WHERE id = ?";
						   System.out.print("Copy and paste to command line if you need to\n" +
					   						"----Original-Headline----\n" + editText + "\n");break;
					case 2:editText = rset.getClob("sub_headline").getSubString(1l, (int)rset.getClob("sub_headline").length());
					   	   sql +=  "sub_headline = ? WHERE id = ?";
					   	   System.out.print("Copy and paste to command line if you need to\n" +
					   			   			"--Original-Sub_Headline--\n" + editText + "\n");break;
					case 3:editText = rset.getClob("body").getSubString(1l, (int)rset.getClob("body").length());
					   	   sql +=  "body = ? WHERE id = ?";
					   	   System.out.print("Copy and paste to command line if you need to\n" +
					   			   			"------Original-Body------\n" + editText + "\n");break;
					case 4:sql +=  "section = ? WHERE id = ?";
					   	   System.out.print("Enter the number for the section:\n" +
					   			   			"1) News\n" +
					   			   			"2) Op-Ed\n" +
					   			   			"3) LuteLife\n" +
					   			   			"4) International\n" +
					   			   			"5) Arts & Intertainment\n" +
					   			   			"6) Sports\n" +
					 	 					"---> ");break;
					case 5:sql +=  "rank = ? WHERE id = ?";
				       	   System.out.println("What rank should the article be:\n");break;
					case 6:sql +=  "status = ? WHERE id = ?";
				       	   System.out.print("Enter 2 for revised, or 3 for candidate:\n");break;
				}
				editText = in.nextLine();
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, editText);
				pstmt.setInt(2, id);
			
				pstmt.execute();
				conn.commit();
				
				sql = "INSERT INTO edit " +
					  "VALUES(?, ?)";
				pstmt = conn.prepareStatement(sql);
				
				pstmt.setString(1, epass);
				pstmt.setInt(2, id);
				
				pstmt.execute();
				conn.commit();
			}
		} catch(Exception e) {
			System.out.println("caught exception" + e.toString());
		}
	}
	
	/**
	 * Creates a new user for the database
	 * 
	 * @author oconnecp
	 */
	public void newUser(String ePass, String fName, String mName, String lName,
			byte role) {

		String sql = null;

		try {
			// create a Statement and an SQL string for the statement
			sql = "INSERT INTO users "+
				  "VALUES      (?, ?, ?, ?, ?)";
			
			PreparedStatement pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, ePass);
			pstmt.setString(2, fName);
			pstmt.setString(3, mName);
			pstmt.setString(4, lName);
			pstmt.setInt(5, role);
			
			pstmt.executeQuery(sql);
			conn.commit();
		} catch (SQLException e) {
			System.out.println("createStatement " + e.getMessage() + sql);
		}
	}

	/**
	 * Returns a set of articles that match the attributes given.
	 * 
	 * @param section
	 *            The section in which to search for articles. -1 for any
	 *            section.
	 * @param articleDate
	 *            The date of original authorship to search for. null for any
	 *            date.
	 * @param headlineKey
	 *            Search key for headlines. Any headline containing this key
	 *            will be matched. null for any headline.
	 * @param issue
	 *            The issue date in which to search. null for any issue date.
	 * @return A set of articles that match the attributes given.
	 * @throws SQLException
	 *             Upon error in executing the SQL query.
	 * @throws NullPointerException
	 *             If the Connection object has not yet been initialized, i.e.,
	 *             if the Utilities object has not yet connected to the
	 *             database.
	 * @author Fredrik
	 */
	public ResultSet searchArchive(int section, Date articleDate,
			String headlineKey, Date issue) throws SQLException,
			NullPointerException {
		ResultSet ret = null;
		StringBuffer sqlBuffer = new StringBuffer();
		String constraintPrefix = " WHERE ";

		sqlBuffer.append("SELECT * FROM article");

		if (section != -1) {
			sqlBuffer.append(constraintPrefix + "section = ?");
			constraintPrefix = " AND ";
		}

		if (articleDate != null) {
			sqlBuffer.append(constraintPrefix + "article_date = ?");
			constraintPrefix = " AND ";
		}

		if (headlineKey != null) {
			sqlBuffer.append(constraintPrefix+"headline LIKE(?)");
			constraintPrefix = " AND ";
		}

		if (issue != null)
			sqlBuffer.append(constraintPrefix + "issue = ?");

		sqlBuffer.append(" ORDER BY id");
				
		PreparedStatement pstmt = conn.prepareStatement(sqlBuffer.toString());

		int index = 1;
		if (section != -1)
			pstmt.setInt(index++, section);
		if (articleDate != null)
			pstmt.setDate(index++, articleDate);
		if (headlineKey != null)
			pstmt.setString(index++, "%"+headlineKey+"%");
		if (issue != null)
			pstmt.setDate(index, issue);

		ret = pstmt.executeQuery();
		conn.commit();

		return ret;
	}
	
	/**
	 * Given an article ID, image description, photo credit, and input streams
	 * containing large and small image data, inserts a picture into the
	 * database.
	 * @param article The ID of the article which the picture supplements.
	 * @param alt The image description text.
	 * @param credit The photo credit.
	 * @param smallFile The image data for the small version of the picture.
	 * @param largeFile The image data for the large version of the picture.
	 * @throws SQLException Upon database error.
	 * @throws IOException Upon input stream error.
	 * @author Fredrik
	 * @author Chris
	 * @author Brad
	 */
	public void insertPicture(int article,
							  String alt,
							  String credit,
							  InputStream smallFile,
							  InputStream largeFile)
	throws SQLException, IOException {
		// Insert scaffolding and picture metadata.
		String sql = "INSERT INTO picture (id," +
			  		 "						article," +
					 "						alt," +
					 "						credit," +
					 "						small_file," +
					 "						large_file) " +
					 "VALUES      (picture_id_increment.nextval," +
					 "				?," +
					 "				?," +
					 "				?," +
					 "				EMPTY_BLOB()," +
					 "				EMPTY_BLOB())";
		PreparedStatement pstmt = conn.prepareStatement(sql);
					
		pstmt.setInt(1, article);
		
		pstmt.setString(2, alt);
		pstmt.setString(3, credit);
		
		pstmt.executeUpdate();
		
		// Grab BLOBs from database.
		sql = "SELECT small_file, large_file " +
			  "FROM   picture " +
			  "WHERE  id = (SELECT MAX (id) FROM picture) " +
			  "FOR UPDATE";
		
		ResultSet rs = conn.prepareStatement(sql).executeQuery();
		rs.next();
		
		BLOB smallBlob = ((OracleResultSet)rs).getBLOB("small_file");
		BLOB largeBlob = ((OracleResultSet)rs).getBLOB("large_file");
		
		// Write small file.
		int    chunkSize    = smallBlob.getChunkSize();
        byte[] binaryBuffer = new byte[chunkSize];
        
        int position = 1;
        int bytesRead;
        while ((bytesRead = smallFile.read(binaryBuffer)) != -1) {
            smallBlob.putBytes(position, binaryBuffer, bytesRead);
            position += bytesRead;
        }
        
        // Write large file.
        chunkSize = largeBlob.getChunkSize();
        binaryBuffer = new byte[chunkSize];
        
        position  = 1;
        bytesRead = 0;
        while ((bytesRead = largeFile.read(binaryBuffer)) != -1) {
        	largeBlob.putBytes(position, binaryBuffer, bytesRead);
        	position += bytesRead;
        }
        
        // Commit
		conn.commit();
	}
	
	/**
	 * Deletes a picture tuple from the database.
	 * @param picID The ID of the picture tuple to delete.
	 * @throws SQLException Upon database error.
	 * @author Brad Ballinger
	 */
	public void deletePicture(int picID) throws SQLException {
		String sql = "DELETE FROM picture " +
		  "WHERE ID = ?";
		PreparedStatement pstmt = null;
		pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, picID);
		pstmt.executeQuery();
		conn.commit();
	}
}
